package com.service;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DateUtil;

import util.PinYinTool;

/**
 * 根据Excel动态创建表及插入数据
 * @author SHANG
 * @version V1.0
 */
public class Excel2Table {
    private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;
    private HSSFRow row1;
    private HSSFCell cell;
    private String namecn;
    
    private final String VARCHAR="varchar(255)";
    private final String NUM="numeric";
    private final String DATE="date";
    private final String BL ="boolean";
    /**
     * 读取Excel中sheet的名字
     * @param InputStream
     * @return String[] sheet名字的数组
     */
    public String[] getSheetName(InputStream is){
    	try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
    	
    	//获取sheet页总数
    	int sheetNum = wb.getNumberOfSheets();
        String[] names = new String[sheetNum];
        for (int i = 0; i < sheetNum; i++) {
        	sheet=wb.getSheetAt(i);
        	namecn=sheet.getSheetName();
        	names[i]= PinYinTool.getPingYin(namecn);
		}
        return names;
    }
    
    /**
     * 根据excel创建table
     * @param InputStream
     * @return List<String> 创建table的sql语句
     */
    @SuppressWarnings("deprecation")
	public List<String> createTableByExcel(InputStream is) {
    	
    	try {
    		fs = new POIFSFileSystem(is);
    		wb = new HSSFWorkbook(fs);
    	} catch (IOException e) {
    		e.printStackTrace();
    	}
    	
    	List<String> list = new ArrayList<String>();
    	//获取sheet页总数
    	int sheetNum = wb.getNumberOfSheets();
    	String[] names = new String[sheetNum];
        for (int i = 0; i < sheetNum; i++) {	//获取每个sheet的名字,作为table的名字
        	sheet=wb.getSheetAt(i);
        	namecn=sheet.getSheetName();
        	names[i]= PinYinTool.getPingYin(namecn);
		}
        //创建table的sql语句
        StringBuffer sql = new StringBuffer();
        String cellType;
        String titlecn;
        //对每一个sheet进行循环
    	for(int index=0;index<sheetNum;index++){
    		
    		sheet = wb.getSheetAt(index);
    		row = sheet.getRow(0);
    		row1= sheet.getRow(1);
//    		sql+="drop table if exists d"+names[index]+";";
    		sql.append("create table d"+names[index]
    				+"(id int primary key auto_increment,");
    				
    		// 标题总列数
    		int colNum = row.getPhysicalNumberOfCells();
    		String[] title = new String[colNum];
    		for (int i = 0; i < colNum; i++) {
//    			title[i] = getStringCellValue(row.getCell((short) i));
    			//字段名
    			titlecn = getCellFormatValue(row.getCell((short) i));
    			title[i] = PinYinTool.getPingYin(titlecn);
    			sql.append(title[i]);
    			//字段类型
    			cellType=this.getCellTypeByFormat(row1.getCell((short)i));
    			sql.append(" "+cellType);
    			if(i!=colNum-1){
    				sql.append(",");
    			}
    		}
    		sql.append(");");
    		list.add(sql.toString());
    		sql.setLength(0);
    		
    	}
    	System.out.println(list);
    	return list;
    }

    /**
     * 读取Excel数据内容,插入到数据库表中
     * @param InputStream
     * @return List 包含单元格数据内容的List对象
     */
    @SuppressWarnings("deprecation")
	public List<Map<Integer, String>> insertDataToTable(InputStream is) {

    	String[] names = this.getSheetName(is);
        
    	List<Map<Integer, String>> list = new ArrayList<Map<Integer,String>>();
    	String cellValue;
    	
    	//对sheet进行循环
    	for(int index=0;index<names.length;index++)
    	{
    		Map<Integer, String> content = new HashMap<Integer, String>();
    		StringBuilder sb = new StringBuilder();
    		
    		sheet = wb.getSheetAt(index);
    		// 得到总行数
    		int rowNum = sheet.getLastRowNum();
    		row = sheet.getRow(0);
    		int colNum = row.getPhysicalNumberOfCells();
    		// 正文内容应该从第二行开始,第一行为表头的标题
    		// 对每行的内容进行循环
    		for (int i = 1; i <= rowNum; i++) {
    			sb.append("insert into d"+names[index]+" values(null,");
    			row = sheet.getRow(i);
    			int j = 0;
    			while (null != row && j < colNum) {
    				cell=row.getCell((short)j);
    				if(this.isNumeric(cell)){
    					sb.append(getCellFormatValue(cell).trim());	//如果是纯数字，则sql语句中不加单引号
    				}else{
    					sb.append("'"+getCellFormatValue(cell).trim()+"'");
    				}
    				cellValue=getCellFormatValue(cell).trim();
    				
    				if(j!=colNum-1){
    					sb.append(",");
    				}
    				j++;
    			}
    			sb.append(");");
    			content.put(i, sb.toString());
    			sb.setLength(0);
    		}
    		
    		list.add(content);
    	}
    	System.out.println(list);
    	return list;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     * 根据单元格不同属性返回字符串数值
     * 
     * @param cell Excel单元格
     * @return String 单元格数据内容
     * @deprecated 
     */
    private String getStringCellValue(HSSFCell cell) {
        String strCell = "";
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strCell = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            strCell = String.valueOf(cell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strCell = String.valueOf(cell.getBooleanCellValue());
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strCell = "";
            break;
        default:
            strCell = "";
            break;
        }
        if (strCell.equals("") || strCell == null) {
            return "";
        }
        return strCell;
    }

    /**
     * 根据单元格不同属性返回单元格类型
     * 
     * @param cell Excel单元格
     * @return String 单元格类型
     */
    private String getCellTypeByFormat(HSSFCell cell) {
    	String strType;

        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            strType = VARCHAR;
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
        	if (DateUtil.isCellDateFormatted(cell)) {
                strType=DATE;
            } else {
                System.out.println(cell.getNumericCellValue());
                strType=NUM;
            }
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            strType = BL;
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            strType = "";
            break;
        default:
            strType = "";
            break;
        }
        if (strType.equals("") || strType == null) {
            return "";
        }
        return strType;
    }
    
    /**
     * 获取单元格数据内容为日期类型的数据，暂时未用到
     * 
     * @param cell
     *            Excel单元格
     * @return String 单元格数据内容
     */
    @SuppressWarnings("deprecation")
	private String getDateCellValue(HSSFCell cell) {
        String result = "";
        try {
            int cellType = cell.getCellType();
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
                Date date = cell.getDateCellValue();
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)
                        + "-" + date.getDate();
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {
                String date = getStringCellValue(cell);
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {
                result = "";
            }
        } catch (Exception e) {
            System.out.println("日期格式不正确!");
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    private String getCellFormatValue(HSSFCell cell) {
        String cellvalue = "";
        if (cell != null) {
        	
        	String cellValue = "";
        	
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Date格式
                    
                    //方法1：这样子的date格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    
                    //方法2：这样子的date格式是不带时分秒的：2011-10-12
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                    
                }else {		// 如果是纯数字,取得当前Cell的数值
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }
    /**
     * 判断cell类型是否为数字
     * @param cell
     * @return Boolean
     */
    private boolean isNumeric(HSSFCell cell) {
    	boolean flag=false;
    	if (cell != null) {
    		// 判断当前Cell的Type
    		switch (cell.getCellType()) {
    		// 如果当前Cell的Type为NUMERIC
    		case HSSFCell.CELL_TYPE_NUMERIC:
    			// 判断当前的cell是否为Date
    			if (HSSFDateUtil.isCellDateFormatted(cell)) {
    				flag=false;
    			}else {		// 如果是纯数字
    				flag=true;
    			}
    			break;
    		default:
    			flag=false;
    		}
    	} else {
    		flag=false;
    	}
    	return flag;
    }
}
